ALTER FUNCTION fn_get_shiptime_yield( @processStep_Id INT, @fg_ProcessStep_Id INT, @path NVARCHAR(1000), @flag INT )
RETURNS @return TABLE ( Total_Ship_Time INT, Total_Yield FLOAT ) AS
BEGIN
	DECLARE @flow_cursor CURSOR
	DECLARE @Temp_ProcessStep_Id INT
	DECLARE @begin_Process INT 
	DECLARE @ps_type_name VARCHAR(50)
	DECLARE @cycle_time INT
	DECLARE @ship_time INT
	DECLARE @yeild NUMERIC(6, 2)
	DECLARE @locationId INT
	DECLARE @prev_location_id INT
	DECLARE @prev_processStep_id INT
	DECLARE @expected_die_wafer FLOAT
	DECLARE @gross_die_per_wafer INT
	DECLARE @chkVal INT

	DECLARE @total_ship_time INT
	DECLARE @total_yeild FLOAT

	SET @begin_Process = 0
	SET @total_ship_time = 0
	SET @total_yeild = 1
	SET @flow_cursor = CURSOR FOR SELECT ProcessStep_Id, PS_Type_Name FROM fn_get_process_flow_from_pathString ( @path )
	
	OPEN @flow_cursor
	FETCH NEXT FROM @flow_cursor INTO @Temp_ProcessStep_Id, @ps_type_name
	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF @ps_type_name like 'Finished Goods'
		BEGIN
			SET @ship_time  = null
			
			SELECT TOP 1 @locationId = Location_Id FROM Process_Step_Location_Table WHERE ProcessStep_ID = @Temp_ProcessStep_Id ORDER BY PSLT_PRIORITY
			
			IF @prev_location_id IS NOT NULL
			BEGIN 
				SELECT TOP 1 @ship_time = MAX( TT_Normal_Time ) FROM Transportation_Table WHERE ProcessStep_ID = @Temp_ProcessStep_Id AND TT_Destination_Location_ID = @locationId AND TT_Source_Location_ID = @prev_location_id
			END
			
			IF @ship_time IS NULL
			BEGIN
				SET @ship_time = 0
				-- We need to fetch the Previous Location when there is no records in Transportation_Table
				IF @prev_location_id IS NULL or @prev_location_id = 0
				BEGIN
					SELECT TOP 1 @prev_processStep_Id = ProcessStep_Id from Process_Step_Traverse_Table WHERE Next_ProcessStep_ID = @Temp_ProcessStep_Id
					SELECT TOP 1 @prev_location_id = Location_Id FROM Process_Step_Location_Table WHERE ProcessStep_ID = @prev_processStep_Id ORDER BY PSLT_PRIORITY
				END	
				SELECT TOP 1 @ship_time = MAX(TT_Normal_Time) FROM Transportation_Table WHERE TT_Source_Location_ID = @prev_location_id and TT_destination_Location_ID = @locationId
				IF @ship_time is null 
				BEGIN
					SET @ship_time = 0
				END
			END

			IF @begin_Process = 0
			BEGIN
				SET @begin_Process = 1
				SET @flag = 0
			END
			ELSE
			BEGIN
				SET @total_ship_time = @total_ship_time + @ship_time 
			END
			SET @prev_location_id = @locationId
		END
		ELSE IF @ps_type_name like 'Mark%'
		BEGIN
			SET @ship_time  = null
			SET @cycle_time = null	
			
			SELECT TOP 1 @cycle_time = ISNULL( PSLT_Cycle_Time_Normal, 0), @locationId = Location_Id FROM Process_Step_Location_Table WHERE ProcessStep_ID = @Temp_ProcessStep_Id  ORDER BY PSLT_Priority  -- and Location_ID = @source_locationId

			IF @cycle_time IS NULL
			BEGIN
				SET @cycle_time = 0				
			END				

			IF @prev_location_id IS NOT NULL
			BEGIN 
				SELECT TOP 1 @ship_time = MAX( TT_Normal_Time ) FROM Transportation_Table WHERE ProcessStep_ID = @Temp_ProcessStep_Id AND TT_Destination_Location_ID = @locationId AND TT_Source_Location_ID = @prev_location_id
			END
			
			IF @ship_time IS NULL
			BEGIN
				SET @ship_time = 0
				-- We need to fetch the Previous Location when there is no records in Transportation_Table
				IF @prev_location_id IS NULL or @prev_location_id = 0
				BEGIN
					SELECT TOP 1 @prev_processStep_Id = ProcessStep_Id from Process_Step_Traverse_Table WHERE Next_ProcessStep_ID = @Temp_ProcessStep_Id
					SELECT TOP 1 @prev_location_id = Location_Id FROM Process_Step_Location_Table WHERE ProcessStep_ID = @prev_processStep_Id ORDER BY PSLT_PRIORITY
				END	
				SELECT TOP 1 @ship_time = MAX(TT_Normal_Time) FROM Transportation_Table WHERE TT_Source_Location_ID = @prev_location_id and TT_destination_Location_ID = @locationId
				IF @ship_time is null 
				BEGIN
					SET @ship_time = 0
				END
			END
			
			IF @begin_Process = 0
			BEGIN
				IF @flag = 0
				BEGIN
					SET @total_ship_time = @total_ship_time + @cycle_time
				END

				SET @flag = 0
				SET @begin_Process = 1
			END
			ELSE
			BEGIN
				SET @total_ship_time = @total_ship_time + @ship_time + @cycle_time
			END
			SET @prev_location_id = @locationId
		END
		ELSE IF @ps_type_name = 'Class Store'
		BEGIN
			SET @ship_time = null
			
			SELECT TOP 1 @locationId = Location_Id FROM Process_Step_Location_Table WHERE ProcessStep_ID = @Temp_ProcessStep_Id ORDER BY PSLT_PRIORITY
			
			IF @prev_location_id IS NOT NULL
			BEGIN 
				SELECT TOP 1 @ship_time = MAX( TT_Normal_Time ) FROM Transportation_Table WHERE ProcessStep_ID = @Temp_ProcessStep_Id AND TT_Destination_Location_ID = @locationId AND TT_Source_Location_ID = @prev_location_id
			END
			
			IF @ship_time IS NULL
			BEGIN
				SET @ship_time = 0
				-- We need to fetch the Previous Location when there is no records in Transportation_Table
				IF @prev_location_id IS NULL or @prev_location_id = 0
				BEGIN
					SELECT TOP 1 @prev_processStep_Id = ProcessStep_Id from Process_Step_Traverse_Table WHERE Next_ProcessStep_ID = @Temp_ProcessStep_Id
					SELECT TOP 1 @prev_location_id = Location_Id FROM Process_Step_Location_Table WHERE ProcessStep_ID = @prev_processStep_Id ORDER BY PSLT_PRIORITY
				END	
				SELECT TOP 1 @ship_time = MAX(TT_Normal_Time) FROM Transportation_Table WHERE TT_Source_Location_ID = @prev_location_id and TT_destination_Location_ID = @locationId
				IF @ship_time is null 
				BEGIN
					SET @ship_time = 0
				END
			END

			IF @begin_Process = 0
			BEGIN
				SET @begin_Process = 1
				SET @flag = 0
			END
			ELSE
			BEGIN
				SET @total_ship_time = @total_ship_time + @ship_time
			END
			SET @prev_location_id = @locationId
		END
		ELSE IF @ps_type_name = 'Test'
		BEGIN
			SET @ship_time = null
			SET @cycle_Time = null
			SET @yeild = null
				
			--SELECT @yeild = ISNULL( FTT_Target_Yield, 100) FROM Final_Test_Table WHERE ProcessStep_ID = @Temp_ProcessStep_Id
			SELECT @chkVal = ISNUMERIC( ISNULL( Parameter_Value, 100 ) ) FROM Process_Parameter WHERE ProcessStep_ID = @Temp_ProcessStep_Id AND Parameter_Key = 'Target Yield'
			IF @chkVal = 1
			BEGIN
				SELECT @yeild = ISNULL( Parameter_Value, 100) FROM Process_Parameter WHERE ProcessStep_ID = @Temp_ProcessStep_Id AND Parameter_Key = 'Target Yield'
			END
			
			IF( @yeild IS null or @yeild = 0 OR @yeild > 100 )
			BEGIN
				SET @yeild = 100
			END	
		
			SET @yeild = @yeild/100
			SET @total_yeild = @total_yeild * ISNULL ( @yeild, 1 )
			
			-- Fetch the Cycle Time For the most priority Vendor
			SELECT TOP 1 @cycle_Time = ISNULL( PSLT_Cycle_Time_Normal, 0 ), @locationId = Location_Id  FROM Process_Step_Location_Table WHERE ProcessStep_ID = @Temp_ProcessStep_Id ORDER BY PSLT_Priority  --and Location_ID = @source_locationId order By PSLT_PRIORITY

			IF @cycle_time IS NULL
			BEGIN
				SET @cycle_time = 0				
			END

			IF @prev_location_id IS NOT NULL
			BEGIN 
				SELECT TOP 1 @ship_time = MAX( TT_Normal_Time ) FROM Transportation_Table WHERE ProcessStep_ID = @Temp_ProcessStep_Id AND TT_Destination_Location_ID = @locationId AND TT_Source_Location_ID = @prev_location_id
			END
			
			IF @ship_time IS NULL
			BEGIN
				SET @ship_time = 0
				-- We need to fetch the Previous Location when there is no records in Transportation_Table
				IF @prev_location_id IS NULL or @prev_location_id = 0
				BEGIN
					SELECT TOP 1 @prev_processStep_Id = ProcessStep_Id from Process_Step_Traverse_Table WHERE Next_ProcessStep_ID = @Temp_ProcessStep_Id
					SELECT TOP 1 @prev_location_id = Location_Id FROM Process_Step_Location_Table WHERE ProcessStep_ID = @prev_processStep_Id ORDER BY PSLT_PRIORITY
				END	
				SELECT TOP 1 @ship_time = MAX(TT_Normal_Time) FROM Transportation_Table WHERE TT_Source_Location_ID = @prev_location_id and TT_destination_Location_ID = @locationId
				IF @ship_time is null 
				BEGIN
					SET @ship_time = 0
				END
			END
			
			IF @begin_Process = 0
			BEGIN
				IF @flag = 0 
				BEGIN
					SET @total_ship_time = @total_ship_time + @cycle_time
				END
				SET @flag = 0 
				SET @begin_Process = 1
			END
			ELSE
			BEGIN
				SET @total_ship_time = @total_ship_time + @ship_time + @cycle_time
			END
			SET @prev_location_id = @locationId
		END
		ELSE IF @ps_type_name = 'Assembly'
		BEGIN
			SET @ship_time = null
			SET @cycle_Time = null
			SET @yeild = null

			--SELECT @yeild = ISNULL( AT_Target_Yield, 100 ) FROM Assembly_Table WHERE ProcessStep_ID = @Temp_ProcessStep_Id 
			SELECT @chkVal = ISNUMERIC( ISNULL( Parameter_Value, 100 ) ) FROM Process_Parameter WHERE ProcessStep_ID = @Temp_ProcessStep_Id AND Parameter_Key = 'Target Yield'
			IF @chkVal = 1
			BEGIN
				SELECT @yeild = ISNULL( Parameter_Value, 100) FROM Process_Parameter WHERE ProcessStep_ID = @Temp_ProcessStep_Id AND Parameter_Key = 'Target Yield'
			END

			IF( @yeild IS NULL OR @yeild = 0 OR @yeild > 100 ) 
			BEGIN
				SET @yeild = 100
			END	

			SET @yeild = @yeild/100
			SET @total_yeild = @total_yeild * ISNULL ( @yeild, 1 )

			-- Fetch the Cycle Time for Most Priority Vendor
			SELECT TOP 1 @cycle_Time = ISNULL(PSLT_Cycle_Time_Normal, 0), @locationId = Location_Id FROM Process_Step_Location_Table where ProcessStep_ID = @Temp_ProcessStep_Id ORDER BY PSLT_Priority --and Location_ID = @source_locationId

			IF @cycle_time IS NULL
			BEGIN
				SET @cycle_time = 0				
			END

			IF @prev_location_id IS NOT NULL
			BEGIN 
				SELECT TOP 1 @ship_time = MAX( TT_Normal_Time ) FROM Transportation_Table WHERE ProcessStep_ID = @Temp_ProcessStep_Id AND TT_Destination_Location_ID = @locationId AND TT_Source_Location_ID = @prev_location_id
			END
			
			IF @ship_time IS NULL
			BEGIN
				SET @ship_time = 0
				-- We need to fetch the Previous Location when there is no records in Transportation_Table
				IF @prev_location_id IS NULL or @prev_location_id = 0
				BEGIN
					SELECT TOP 1 @prev_processStep_Id = ProcessStep_Id from Process_Step_Traverse_Table WHERE Next_ProcessStep_ID = @Temp_ProcessStep_Id
					SELECT TOP 1 @prev_location_id = Location_Id FROM Process_Step_Location_Table WHERE ProcessStep_ID = @prev_processStep_Id ORDER BY PSLT_PRIORITY
				END	
				SELECT TOP 1 @ship_time = MAX(TT_Normal_Time) FROM Transportation_Table WHERE TT_Source_Location_ID = @prev_location_id and TT_destination_Location_ID = @locationId
				IF @ship_time is null 
				BEGIN
					SET @ship_time = 0
				END
			END

			IF @begin_Process = 0
			BEGIN
				IF @flag = 0 
				BEGIN
					SET @total_ship_time = @total_ship_time + @cycle_time
				END
				SET @begin_Process = 1
				SET @flag = 0
			END
			ELSE
			BEGIN
				SET @total_ship_time = @total_ship_time + @ship_time + @cycle_time
			END
			SET @prev_location_id = @locationId
		END
		ELSE IF @ps_type_name = 'Die Bank'
		BEGIN
			SET @ship_time = null

			SELECT TOP 1 @locationId = Location_Id FROM Process_Step_Location_Table WHERE ProcessStep_ID = @Temp_ProcessStep_Id ORDER BY PSLT_PRIORITY
			
			IF @prev_location_id IS NOT NULL
			BEGIN 
				SELECT TOP 1 @ship_time = MAX( TT_Normal_Time ) FROM Transportation_Table WHERE ProcessStep_ID = @Temp_ProcessStep_Id AND TT_Destination_Location_ID = @locationId AND TT_Source_Location_ID = @prev_location_id
			END
			
			IF @ship_time IS NULL
			BEGIN
				SET @ship_time = 0
				-- We need to fetch the Previous Location when there is no records in Transportation_Table
				IF @prev_location_id IS NULL or @prev_location_id = 0
				BEGIN
					SELECT TOP 1 @prev_processStep_Id = ProcessStep_Id from Process_Step_Traverse_Table WHERE Next_ProcessStep_ID = @Temp_ProcessStep_Id
					SELECT TOP 1 @prev_location_id = Location_Id FROM Process_Step_Location_Table WHERE ProcessStep_ID = @prev_processStep_Id ORDER BY PSLT_PRIORITY
				END	
				SELECT TOP 1 @ship_time = MAX(TT_Normal_Time) FROM Transportation_Table WHERE TT_Source_Location_ID = @prev_location_id and TT_destination_Location_ID = @locationId
				IF @ship_time is null 
				BEGIN
					SET @ship_time = 0
				END
			END
		
			IF @begin_Process = 0
			BEGIN
				SET @begin_Process = 1
				SET @flag = 0
			END
			ELSE
			BEGIN
				SET @total_ship_time = @total_ship_time + @ship_time
			END
			SET @prev_location_id = @locationId
		END
		ELSE IF @ps_type_name = 'SORT'
		BEGIN
			SET @ship_time = null
			SET @cycle_Time = null 		
			SET @expected_die_wafer = 0
			
			--SELECT @expected_die_wafer = ISNULL( ST_Expected_Net_Die_Wafer, 0 ) FROM sort_table WHERE ProcessStep_ID = @Temp_ProcessStep_Id
			SELECT @chkVal = ISNUMERIC( ISNULL( Parameter_Value, 0 ) ) FROM Process_Parameter WHERE ProcessStep_ID = @Temp_ProcessStep_Id AND Parameter_Key = 'Expected Net Die/Wafer'
			IF @chkVal = 1
			BEGIN
				SELECT @expected_die_wafer = ISNULL( Parameter_Value, 0 ) FROM Process_Parameter WHERE ProcessStep_ID = @Temp_ProcessStep_Id AND Parameter_Key = 'Expected Net Die/Wafer'
			END

			SET @gross_die_per_wafer = 0
			DECLARE @Fab_ProcessStep_Id varchar(50)
			SELECT TOP 1 @Fab_ProcessStep_Id = ProcessStep_ID FROM fn_get_process_flow_by_processstep_id ( @Temp_ProcessStep_Id ) where Process_Type_Name = 'Fab'

			--SELECT @gross_die_per_wafer = ISNULL( FT_Gross_Die_Wafer, 0) FROM Fab_Table WHERE ProcessStep_ID = @Fab_ProcessStep_Id
			SELECT @chkVal = ISNUMERIC( ISNULL( Parameter_Value, 0 ) ) FROM Process_Parameter WHERE ProcessStep_ID = @Fab_ProcessStep_Id AND Parameter_Key = 'Gross Die/Wafer'
			IF @chkVal = 1
			BEGIN
				SELECT @gross_die_per_wafer = ISNULL( Parameter_Value, 0 ) FROM Process_Parameter WHERE ProcessStep_ID = @Fab_ProcessStep_Id AND Parameter_Key = 'Gross Die/Wafer'
			END

			IF @expected_die_wafer = null OR @expected_die_wafer = 0 OR @gross_die_per_wafer = 0 OR @gross_die_per_wafer = null
			BEGIN
				SET @yeild = 1
			END
			ELSE
			BEGIN
				SET @yeild = ( @expected_die_wafer / @gross_die_per_wafer )
			END
			
			IF( @yeild = null or @yeild <= 0 )
			BEGIN
				SET @yeild = 1
			END
			SET @total_yeild = @total_yeild * ISNULL( @yeild, 1 )
			
			-- Fetch the Cycle Time for Most Priority Vendor
			SELECT TOP 1 @cycle_Time = isnull( PSLT_Cycle_Time_Normal, 0 ), @locationId = Location_Id FROM Process_Step_Location_Table WHERE ProcessStep_ID = @Temp_ProcessStep_Id ORDER BY PSLT_Priority --and Location_ID = @source_locationId

			IF @cycle_time IS NULL
			BEGIN
				SET @cycle_time = 0				
			END		

			IF @prev_location_id IS NOT NULL
			BEGIN 
				SELECT TOP 1 @ship_time = MAX( TT_Normal_Time ) FROM Transportation_Table WHERE ProcessStep_ID = @Temp_ProcessStep_Id AND TT_Destination_Location_ID = @locationId AND TT_Source_Location_ID = @prev_location_id
			END
			
			IF @ship_time IS NULL
			BEGIN
				SET @ship_time = 0
				-- We need to fetch the Previous Location when there is no records in Transportation_Table
				IF @prev_location_id IS NULL or @prev_location_id = 0
				BEGIN
					SELECT TOP 1 @prev_processStep_Id = ProcessStep_Id from Process_Step_Traverse_Table WHERE Next_ProcessStep_ID = @Temp_ProcessStep_Id
					SELECT TOP 1 @prev_location_id = Location_Id FROM Process_Step_Location_Table WHERE ProcessStep_ID = @prev_processStep_Id ORDER BY PSLT_PRIORITY
				END	
				SELECT TOP 1 @ship_time = MAX(TT_Normal_Time) FROM Transportation_Table WHERE TT_Source_Location_ID = @prev_location_id and TT_destination_Location_ID = @locationId
				IF @ship_time is null 
				BEGIN
					SET @ship_time = 0
				END
			END

			IF @begin_Process = 0
			BEGIN
				IF @flag = 0
				BEGIN
					SET @total_ship_time = @total_ship_time + @cycle_time
				END
				SET @flag = 0
				SET @begin_Process = 1
			END
			ELSE
			BEGIN
				SET @total_ship_time = @total_ship_time + @ship_time + @cycle_time
			END
			SET @prev_location_id = @locationId
		END
		ELSE IF @ps_type_name = 'Wafer Store'
		BEGIN
			SET @ship_time = null

			SELECT TOP 1 @locationId = Location_Id FROM Process_Step_Location_Table WHERE ProcessStep_ID = @Temp_ProcessStep_Id ORDER BY PSLT_PRIORITY
			
			IF @prev_location_id IS NOT NULL
			BEGIN 
				SELECT TOP 1 @ship_time = MAX( TT_Normal_Time ) FROM Transportation_Table WHERE ProcessStep_ID = @Temp_ProcessStep_Id AND TT_Destination_Location_ID = @locationId AND TT_Source_Location_ID = @prev_location_id
			END
			
			IF @ship_time IS NULL
			BEGIN
				SET @ship_time = 0
				-- We need to fetch the Previous Location when there is no records in Transportation_Table
				IF @prev_location_id IS NULL or @prev_location_id = 0
				BEGIN
					SELECT TOP 1 @prev_processStep_Id = ProcessStep_Id from Process_Step_Traverse_Table WHERE Next_ProcessStep_ID = @Temp_ProcessStep_Id
					SELECT TOP 1 @prev_location_id = Location_Id FROM Process_Step_Location_Table WHERE ProcessStep_ID = @prev_processStep_Id ORDER BY PSLT_PRIORITY
				END	
				SELECT TOP 1 @ship_time = MAX(TT_Normal_Time) FROM Transportation_Table WHERE TT_Source_Location_ID = @prev_location_id and TT_destination_Location_ID = @locationId
				IF @ship_time is null 
				BEGIN
					SET @ship_time = 0
				END
			END
			
			IF @begin_Process = 0
			BEGIN
				SET @begin_Process = 1
				SET @flag = 0
			END
			ELSE
			BEGIN
				SET @total_ship_time = @total_ship_time + @ship_time --+ @cycle_time
			END
			SET @prev_location_id = @locationId
		END
		ELSE IF @ps_type_name = 'FAB'
		BEGIN
			SET @cycle_Time = null
			SET @yeild = null

			--SELECT @yeild = ISNULL( FT_Target_Yield, 100 ) from Fab_Table where ProcessStep_ID = @Temp_ProcessStep_Id
			SELECT @chkVal = ISNUMERIC( ISNULL( Parameter_Value, 0 ) ) FROM Process_Parameter WHERE ProcessStep_ID = @Temp_ProcessStep_Id AND Parameter_Key = 'Target Yield'
			IF @chkVal = 1
			BEGIN
				SELECT @yeild = ISNULL( Parameter_Value, 100 ) FROM Process_Parameter WHERE ProcessStep_ID = @Temp_ProcessStep_Id AND Parameter_Key = 'Target Yield'
			END

			IF( @yeild = null or @yeild = 0 )
			BEGIN
				SET @yeild = 100
			END	

			SET @yeild = @yeild/100

			SET @total_yeild = @total_yeild * isnull(@yeild,1)
			
			-- Fetch the Cycle Time for Most Priority Vendor
			SELECT TOP 1 @cycle_Time = ISNULL( PSLT_Cycle_Time_Normal, 0), @locationId = Location_Id FROM Process_Step_Location_Table where ProcessStep_ID = @Temp_ProcessStep_Id ORDER BY PSLT_Priority  -- and Location_ID = @source_locationId
			
			IF @cycle_time IS NULL
			BEGIN
				SET @cycle_time = 0				
			END				
			
			IF @flag = 0
			BEGIN	
				SET @total_ship_time = @total_ship_time + @cycle_Time
			END
			ELSE
			BEGIN
				SET @flag = 0
			END
			
			SET @begin_Process = 1
			SET @prev_location_id = @locationId
		END 		
		FETCH NEXT FROM @flow_cursor INTO @Temp_ProcessStep_Id, @ps_type_name
	END
	CLOSE @flow_cursor
	DEALLOCATE @flow_cursor
	INSERT INTO @return VALUES ( @total_ship_time, @total_yeild )
	RETURN
END